package com.achievo.know.domain.infra.hibernate;

import java.math.BigInteger;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.math.NumberUtils;
import org.apache.commons.lang.time.DateUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.achievo.know.domain.FileAttachment;
import com.achievo.know.domain.FileAttachmentRepository;
import com.achievo.know.domain.News;
import com.achievo.know.domain.NewsRepository;
import com.achievo.know.pojo.AreaConstant;
import com.achievo.know.pojo.SearchResult;
import com.dayatang.spring.repository.BaseEntityRepositoryHibernateSpring;

@Repository
public class NewsRepositoryImpl extends BaseEntityRepositoryHibernateSpring<News, Long>  implements NewsRepository{
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private FileAttachmentRepository fileAttachmentRepository;

	public NewsRepositoryImpl() {
		super(News.class);
	}

	@Override
	public void batchDelete(final String[] ids) {
		final String sql="delete from ramw_news where id=?";
		final String delContentSql="delete from rams_news_content where FK_NEWS_ID=?";
		final String delAreaSql="delete from RAMS_NEWS_R_AREA where NEWS_ID=?";
		final String delFaqSql="delete from rams_faq where FK_ID=? and FK_PARENT_ID is null and FAQ_FROM=2";
		final String delFaqSql1="delete from rams_faq where FK_ID=? and FK_PARENT_ID is not null and FAQ_FROM=2";
		final String delAttachSql="delete from rams_file_attachment where FK_ID=? and (FK_TYPE=2 or FK_TYPE=3)";
		final String delUserFavSql="delete from rams_user_favorite where file_id=?";
		BatchPreparedStatementSetter bss=new BatchPreparedStatementSetter(){

			@Override
			public int getBatchSize() {
				return ids.length;
			}

			@Override
			public void setValues(PreparedStatement ps, int i)
					throws SQLException {
					ps.setLong(1, Long.parseLong(ids[i]));
			}
			
		};
		jdbcTemplate.batchUpdate(delAttachSql, bss);
		jdbcTemplate.batchUpdate(delAreaSql, bss);
		jdbcTemplate.batchUpdate(delContentSql, bss);
		jdbcTemplate.batchUpdate(delFaqSql1, bss);
		jdbcTemplate.batchUpdate(delFaqSql, bss);
		jdbcTemplate.batchUpdate(delUserFavSql, bss);
		jdbcTemplate.batchUpdate(sql, bss);
	}
	
	@Override
	public List<News> findNewsByArea(List<Long> cateIds,final Long type,int limit) {
		String cw="";
		if(cateIds!=null && cateIds.size()>0){
			StringBuilder cb=new StringBuilder();
			for(Long cateId:cateIds){
				cb.append(cateId).append(",");
			}
			if(cb.length()>0){
				cb.deleteCharAt(cb.length()-1);
				cw=" and n.CATEGORY_ID in ("+cb.toString()+")";
			}
		}
		if(type.longValue()==AreaConstant.AREA4){
			cw=cw+" and to_days(curdate())-to_days(n.SUGEST_TIME)=0";
		}
		StringBuilder b=new StringBuilder();
		b.append(" select n.* from RAMW_NEWS n inner join RAMS_NEWS_R_AREA a on n.id=a.news_id and a.area_id=? ").append(cw).append(" order by n.NEWS_POSITION desc,n.PUBLISH_TIME desc limit ?");
		Object[] args=new Object[]{type,limit};
		return getJdbcTemplate().query(b.toString(),args, new RowMapper<News>(){

			@Override
			public News mapRow(ResultSet rs, int rowNum) throws SQLException {
				News n=new News();
				n.setTitle(rs.getString("TITLE"));
				n.setPublishTime(rs.getTimestamp("PUBLISH_TIME"));
				n.setCategory(rs.getString("CATEGORY"));
				n.setPosition(rs.getInt("NEWS_POSITION"));
				n.setId(rs.getLong("ID"));
				n.setDesc(rs.getString("DESCRIPTION"));
				//if(type.longValue()==AreaConstant.NOTICE || type.longValue()==AreaConstant.AREA4){
					FileAttachment image=fileAttachmentRepository.getImage(n.getId());
					if(image!=null)
						n.setNewsImage(image);
				//}
				return n;
			}
			
		});
	}
	
	

	@Override
	public List<Object> search(String searchText, int currentPage,
			int pageSize) {
		searchText="'%"+searchText+"%'";
		StringBuilder b=new StringBuilder();
		b.append(" select * from (select TITLE,DESCRIPTION,PUBLISH_TIME,PUBLISH_USER_NAME,BROWSER_SUM,-999,0,ID from ramw_news where TITLE like ").append(searchText).append(" or KEYWORD like ").append(searchText).append(" or DESCRIPTION like ").append(searchText).append(" ");
		//b.append(" union all");
		//b.append(" SELECT FILE_NAME,DESCRIPTION,PUBLISH_TIME,PUBLISH_USER,BROWSER_SUM,DOWN_SUM,1,ID from rams_file where STATUS='APPROVE' and FILE_NAME like ").append(searchText).append(" or KEYWORD like ").append(searchText).append(" or DESCRIPTION like ").append(searchText).append(" ");
		b.append(" ) a limit ?,?");
		Session session=getSession();
		SQLQuery q=session.createSQLQuery(b.toString());
		/*q.setFetchSize(pageSize);
		q.setFirstResult((currentPage - 1) * pageSize);*/
		q.setInteger(0, (currentPage - 1) * pageSize);
		q.setInteger(1 , pageSize);
		return q.list();
	}
	
	@Override
	public long searchCount(String searchText) {
		searchText="'%"+searchText+"%'";
		StringBuilder b=new StringBuilder();
		b.append("select count(*) from( select TITLE,DESCRIPTION,PUBLISH_TIME,PUBLISH_USER,BROWSER_SUM,-999,0,ID from ramw_news where TITLE like ").append(searchText).append(" or KEYWORD like ").append(searchText).append(" or DESCRIPTION like ").append(searchText).append(" ");
		//b.append(" union all");
		//b.append(" SELECT FILE_NAME,DESCRIPTION,PUBLISH_TIME,PUBLISH_USER,BROWSER_SUM,DOWN_SUM,1,ID from rams_file where STATUS='APPROVE' and FILE_NAME like ").append(searchText).append(" or KEYWORD like ").append(searchText).append(" or DESCRIPTION like ").append(searchText).append(" ) as a");
		b.append(") as a");
		Session session=getSession();
		SQLQuery q=session.createSQLQuery(b.toString());
		return ((BigInteger) q.uniqueResult()).longValue();
	}

	@Override
	public int getMaxPosition() {
		String sql="select max(NEWS_POSITION) from RAMW_NEWS";
		Session session=getSession();
		SQLQuery q=session.createSQLQuery(sql);
		if(q.uniqueResult()!=null)
			return (Integer) q.uniqueResult();
		return 1;
	}

	@Override
	public List<News> findPageNewsByArea(List<Long> cateIds,final Long type, int currentPage,
			int pageSize) {
		String cw="";
		if(cateIds!=null && cateIds.size()>0){
			StringBuilder cb=new StringBuilder();
			for(Long cateId:cateIds){
				cb.append(cateId).append(",");
			}
			if(cb.length()>0){
				cb.deleteCharAt(cb.length()-1);
				cw=" and n.CATEGORY_ID in ("+cb.toString()+")";
			}
		}
		StringBuilder b=new StringBuilder();
		b.append(" select n.* from RAMW_NEWS n inner join RAMS_NEWS_R_AREA a on n.id=a.news_id and a.area_id=? ").append(cw).append(" order by n.NEWS_POSITION desc,n.PUBLISH_TIME desc limit ?,?");
		Object[] args=new Object[]{type,(currentPage - 1) * pageSize,pageSize};
		return getJdbcTemplate().query(b.toString(),args, new RowMapper<News>(){

			@Override
			public News mapRow(ResultSet rs, int rowNum) throws SQLException {
				News n=new News();
				n.setTitle(rs.getString("TITLE"));
				n.setPublishTime(rs.getTimestamp("PUBLISH_TIME"));
				n.setSugestTime(rs.getTimestamp("SUGEST_TIME"));
				n.setCategory(rs.getString("CATEGORY"));
				n.setPosition(rs.getInt("NEWS_POSITION"));
				n.setBrowserNum(rs.getInt("BROWSER_SUM"));
				n.setPublishUser(rs.getString("PUBLISH_USER_NAME"));
				n.setId(rs.getLong("ID"));
				n.setDesc(rs.getString("DESCRIPTION"));
				if(type.longValue()==AreaConstant.NOTICE || type.longValue()==AreaConstant.AREA4){
					FileAttachment image=fileAttachmentRepository.getImage(n.getId());
					if(image!=null)
						n.setNewsImage(image);
				}
				return n;
			}
			
		});
	}

	@Override
	public long countNewsByArea(List<Long> cateIds,Long type) {
		String cw="";
		if(cateIds!=null && cateIds.size()>0){
			StringBuilder cb=new StringBuilder();
			for(Long cateId:cateIds){
				cb.append(cateId).append(",");
			}
			if(cb.length()>0){
				cb.deleteCharAt(cb.length()-1);
				cw=" and n.CATEGORY_ID in ("+cb.toString()+")";
			}
		}
		StringBuilder b=new StringBuilder();
		b.append(" select count(n.id) from RAMW_NEWS n inner join RAMS_NEWS_R_AREA a on n.id=a.news_id and a.area_id=? ").append(cw);
		Object[] args=new Object[]{type};
		return getJdbcTemplate().queryForLong(b.toString(),args);
	}

	@Override
	public long countByCate(String orderType, String resType,List<Long> cateIds) {
		String cs="";
		StringBuilder cb=new StringBuilder();
		if(cateIds!=null && cateIds.size()>0){
			for(Long cateId:cateIds){
				cb.append(cateId).append(",");
			}
			if(cb.length()>0){
				cb.deleteCharAt(cb.length()-1);
			}else{
				return 0;
			}
			cs=" and CATEGORY_ID in("+cb.toString()+")";
		}
		StringBuilder b=new StringBuilder();
		if("0".equals(resType)){
			b.append(" select count(*) from( select TITLE,DESCRIPTION,PUBLISH_TIME,PUBLISH_USER,BROWSER_SUM,-999,0,ID from ramw_news where 1=1 ").append(cs);
			b.append(" union all");
			b.append(" SELECT FILE_NAME,DESCRIPTION,PUBLISH_TIME,PUBLISH_USER,BROWSER_SUM,DOWN_SUM,1,ID from rams_file where STATUS='APPROVE' ").append(cs).append(" ) as a");
		}
		if("1".equals(resType)){
			b.append(" select count(*) from( select TITLE,DESCRIPTION,PUBLISH_TIME,PUBLISH_USER,BROWSER_SUM,-999,0,ID from ramw_news where 1=1 ").append(cs);
			b.append(" ) as a");
		}
		if("2".equals(resType)){
			b.append(" select count(*) from( SELECT FILE_NAME,DESCRIPTION,PUBLISH_TIME,PUBLISH_USER,BROWSER_SUM,DOWN_SUM,1,ID from rams_file where STATUS='APPROVE' ").append(cs);
			b.append(" ) as a");
		}
		Session session=getSession();
		SQLQuery q=session.createSQLQuery(b.toString());
		return ((BigInteger) q.uniqueResult()).longValue();
	}
	
	@Override
	public List<SearchResult> findPageNewsByCate(String orderType, String resType,List<Long> cateIds,
			int currentPage, int pageSize) {
		StringBuilder cb=new StringBuilder();
		String cs="";
			if(cateIds!=null && cateIds.size()>0){
			for(Long cateId:cateIds){
				cb.append(cateId).append(",");
			}
			if(cb.length()>0){
				cb.deleteCharAt(cb.length()-1);
			}else{
				return new ArrayList();
			}
			cs=" and CATEGORY_ID in("+cb.toString()+")";
		}
		String orderStr="";
		if("1".equals(orderType)){
			orderStr=" order by NEWS_POSITION desc";
		}
		if("2".equals(orderType)){
			orderStr=" order by BROWSER_SUM desc";
		}
		StringBuilder b=new StringBuilder();
		if("0".equals(resType)){
			b.append(" select * from (select TITLE,DESCRIPTION,PUBLISH_TIME,PUBLISH_USER,BROWSER_SUM,-999,0,ID from ramw_news where 1=1 ").append(cs);
			b.append(" union all");
			b.append(" SELECT FILE_NAME,DESCRIPTION,PUBLISH_TIME,PUBLISH_USER,BROWSER_SUM,DOWN_SUM,1,ID from rams_file where STATUS='APPROVE' ").append(cs);
			b.append(" ) a ");
			if("1".equals(orderType)){
				b.append(" order by a.PUBLISH_TIME desc");
			}
			if("2".equals(orderType)){
				b.append(" order by a.BROWSER_SUM desc");
			}
			b.append(" limit ?,?");
		}
		if("1".equals(resType)){
			b.append(" select TITLE,DESCRIPTION,PUBLISH_TIME,PUBLISH_USER,BROWSER_SUM,-999,0,ID from ramw_news where 1=1 ").append(cs).append(orderStr).append(" limit ?,? ");
		}
		if("2".equals(resType)){
			b.append(" SELECT FILE_NAME,DESCRIPTION,PUBLISH_TIME,PUBLISH_USER,BROWSER_SUM,DOWN_SUM,1,ID from rams_file where STATUS='APPROVE' ").append(cs).append(orderStr).append(" limit ?,? ");
		}
		Session session=getSession();
		SQLQuery q=session.createSQLQuery(b.toString());
		q.setInteger(0, (currentPage - 1) * pageSize);
		q.setInteger(1 , pageSize);
		//q.setFetchSize(pageSize);
		//q.setFirstResult((currentPage - 1) * pageSize);
		List<Object> result= q.list();
		List<SearchResult> list=new ArrayList<SearchResult>();
		for(Object mm:result){
			SearchResult s=new SearchResult();
			Object[] m=(Object[])mm;
			if(m[0]!=null)
			s.setTitle(m[0].toString());
			if(m[1]!=null)
			s.setDesc(m[1].toString());
			if(m[2]!=null){
			Date d;
			try {
				d = DateUtils.parseDate(m[2].toString(),new String[]{"yyyy-MM-dd HH:mm:ss.SSS"});
				s.setPublishTime(d);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			}
			if(m[3]!=null)
			s.setPublishUser(m[3].toString());
			if(m[4]!=null)
			s.setBrowserNum(NumberUtils.toInt(m[4].toString()));
			if(m[5]!=null)
			s.setDownSum(NumberUtils.toInt(m[5].toString()));
			if(m[6]!=null)
			s.setFile(NumberUtils.toInt(m[6].toString())>0?true:false);
			if(m[7]!=null)
				s.setId(NumberUtils.createLong(m[7].toString()));
			list.add(s);
		}
		return list;
	}

	
	@Override
	public Long getLid(Long areaId, Long id) {
		String sql="select n.id as id from RAMW_NEWS n inner join RAMS_NEWS_R_AREA a on n.id=a.news_id and a.area_id=? and n.news_position>? order by n.news_position  limit 1";
		Object[] args=new Object[]{areaId,id};
		List<Map<String, Object>> result=getJdbcTemplate().queryForList(sql, args);
		if(result.size()>0)
			return (Long) result.get(0).get("id");
		else
			return -333L;
	}
	
	@Override
	public Long getRid(Long areaId, Long id) {
		String sql="select n.id as id  from RAMW_NEWS n inner join RAMS_NEWS_R_AREA a on n.id=a.news_id and a.area_id=? and n.news_position<?  order by n.news_position desc limit 1";
		Object[] args=new Object[]{areaId,id};
		List<Map<String, Object>> result=getJdbcTemplate().queryForList(sql, args);
		if(result.size()>0)
			return (Long) result.get(0).get("id");
		else
			return -111L;
	}
	
	@Override
	public Long getTop(Long areaId) {
		String sql="SELECT MAX(n.news_position) AS id  FROM RAMW_NEWS n INNER JOIN RAMS_NEWS_R_AREA a ON n.id=a.news_id AND a.area_id=?";
		Object[] args=new Object[]{areaId};
		List<Map<String, Object>> result=getJdbcTemplate().queryForList(sql, args);
		if(result.size()>0)
			return ((Integer) result.get(0).get("id")).longValue();
		else
			return -222L;
	}
	
	@Override
	public Long getLid(Long id) {
		String sql="select n.id as id from RAMW_NEWS n where n.news_position>? order by n.news_position  limit 1";
		Object[] args=new Object[]{id};
		List<Map<String, Object>> result=getJdbcTemplate().queryForList(sql, args);
		if(result.size()>0)
			return (Long) result.get(0).get("id");
		else
			return -333L;
	}

	@Override
	public Long getRid(Long id) {
		String sql="select n.id as id  from RAMW_NEWS n where n.news_position<?  order by n.news_position desc limit 1";
		Object[] args=new Object[]{id};
		List<Map<String, Object>> result=getJdbcTemplate().queryForList(sql, args);
		if(result.size()>0)
			return (Long) result.get(0).get("id");
		else
			return -111L;
	}

	@Override
	public Long getTop() {
		String sql="SELECT MAX(n.news_position) AS id  FROM RAMW_NEWS n";
		Object[] args=new Object[]{};
		List<Map<String, Object>> result=getJdbcTemplate().queryForList(sql, args);
		if(result.size()>0)
			return ((Integer) result.get(0).get("id")).longValue();
		else
			return -222L;
	}
	

	@Override
	public void beforePosition(final News onew, final News nnew) {
		// 	move forward
		if(nnew.getPosition()<onew.getPosition()){
			String sql="update RAMW_NEWS set news_position=news_position-1 where news_position>? and news_position<=?";
			getJdbcTemplate().update(sql, new Object[]{nnew.getPosition(),onew.getPosition()});
			nnew.setPosition(onew.getPosition());
			save(nnew);
		}
		//move backward
		if(nnew.getPosition()>onew.getPosition()){
			String sql="update RAMW_NEWS set news_position=news_position+1 where news_position>? and news_position<?";
			getJdbcTemplate().update(sql, new Object[]{onew.getPosition(),nnew.getPosition()});
			nnew.setPosition(onew.getPosition()+1);
			save(nnew);
		}
		
		
	}
	
	

	@Override
	public boolean isAddFavorite(Long id, String username) {
		String queryString="select count(uf.id) from User u,UserFavorite uf where u.id=uf.userId and u.username=? and uf.fileId=? ";
		Object[] params=new Object[]{username,id};
		Long count=(Long)getSingleResult(queryString, params);
		if(count>0)
			return true;
		return false;
	}
	

	@Override
	public void batchDeleteFavorite(final String[] ids) {
		final String delUserFavSql="delete from rams_user_favorite where file_id=?";
		BatchPreparedStatementSetter bss=new BatchPreparedStatementSetter(){

			@Override
			public int getBatchSize() {
				return ids.length;
			}

			@Override
			public void setValues(PreparedStatement ps, int i)
					throws SQLException {
					ps.setLong(1, Long.parseLong(ids[i]));
			}
			
		};
		jdbcTemplate.batchUpdate(delUserFavSql, bss);
		
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public FileAttachmentRepository getFileAttachmentRepository() {
		return fileAttachmentRepository;
	}

	public void setFileAttachmentRepository(
			FileAttachmentRepository fileAttachmentRepository) {
		this.fileAttachmentRepository = fileAttachmentRepository;
	}
	
	

}
